-- @owner: ningyali
-- @date: 2024-08-12
-- @testpoint: count(column)在dml中的应用-update

--step1:创建测试表1; expect:执行成功
drop table if exists  t_ustore_count_optim_0031_01 cascade;
create table t_ustore_count_optim_0031_01(id1 int primary key,id2 int,id3 smallint not null,id4 int,num1 float,char1 varchar(500) not null,c_date timestamp) WITH (STORAGE_TYPE=USTORE);

--step2:创建序列; expect:执行成功
drop sequence if exists seq_ustore_count_optim_0031;
create sequence seq_ustore_count_optim_0031 increment by 3 start with 1;

--step3:向表中插入数据; expect:执行成功
insert into t_ustore_count_optim_0031_01 values ( seq_ustore_count_optim_0031.nextval , -1 , -2 , 15 , 4041.04329387 , ' A哪吒 ','1924-12-03 00:00:00.068000' ) ;
insert into t_ustore_count_optim_0031_01 values ( seq_ustore_count_optim_0031.nextval , 3  , 4 , 3 , 405.70237548 , ' 010012228 ' ,'1925-12-03 00:00:00.000000') ;
insert into t_ustore_count_optim_0031_01 values ( seq_ustore_count_optim_0031.nextval , -1 , 3 , 30 , 4030.1242131 , ' 数字字符字母 ','1924-12-03 00:00:04.000000' ) ;
insert into t_ustore_count_optim_0031_01 values ( seq_ustore_count_optim_0031.nextval , 4  , 6 , 4 , 401.87754238 , ' 010012228 ','1925-01-03 00:00:05.000000' ) ;
insert into t_ustore_count_optim_0031_01 values ( seq_ustore_count_optim_0031.nextval , 4  , 9 , 0 , 4046.284633 , ' 数字字符字母 ' ,'1929-09-03 00:00:32.000000') ;
insert into t_ustore_count_optim_0031_01 values ( seq_ustore_count_optim_0031.nextval , -2 , 6.6 , 4 , 4003.68558645 , ' 数字字符字母 ','1925-08-03 00:00:00.000000' ) ;
insert into t_ustore_count_optim_0031_01 values ( seq_ustore_count_optim_0031.nextval , -2 , -2 , null , 4044.9145853 , ' ' ,'1925-01-03 00:00:00.020000' ) ;
insert into t_ustore_count_optim_0031_01 values ( seq_ustore_count_optim_0031.nextval , 4  , 9 , 0 , 4035.26588922 , ' 010012228 ','1925-11-03 00:20:00.000000' ) ;
insert into t_ustore_count_optim_0031_01 values ( seq_ustore_count_optim_0031.nextval , -2 , 11 , 2 , 4011.51183805 , ' 010012228 ','1965-10-03 00:00:44.000000' ) ;
insert into t_ustore_count_optim_0031_01 values ( seq_ustore_count_optim_0031.nextval , 2  , 10 , 12 , 4018.35133361 , ' 数字字符字母 ','1925-01-03 00:00:00.000000' ) ;
insert into t_ustore_count_optim_0031_01 values ( seq_ustore_count_optim_0031.nextval , -2 , 7 , 32, 4041.62109974 , ' ilhfjejgYHG ','1925-01-03 00:00:25.000000' ) ;
insert into t_ustore_count_optim_0031_01 values ( seq_ustore_count_optim_0031.nextval , 4  , 1.0 , 11 , 4018.1233257 , ' 数字字符字母 ','1923-01-03 00:30:00.000246' ) ;
insert into t_ustore_count_optim_0031_01 values ( seq_ustore_count_optim_0031.nextval , 1.1  , 55 , 0 , 4018.75686387 , ' 010012228 ' ,'1925-02-03 00:00:10.000000') ;
insert into t_ustore_count_optim_0031_01 values ( seq_ustore_count_optim_0031.nextval , -2 , 7 , -1 , 4001.54253863 , ' gkj^%$ ( )+_-= ' ,'1925-02-03 04:00:00.000000') ;
insert into t_ustore_count_optim_0031_01 values ( seq_ustore_count_optim_0031.nextval , 2 , 2 , -5 , 4046.20763052 , ' ilhfjejgYHG ',null ) ;

--step4:创建测试表2; expect:执行成功
drop table if exists  t_ustore_count_optim_0031_02;
create table t_ustore_count_optim_0031_02(id1 int primary key,id2 int,id3 smallint not null,id4 int,num1 float,char1 varchar(500) not null,c_date timestamp) WITH (STORAGE_TYPE=USTORE);

--step5:向表2中插入数据; expect:执行成功
insert into t_ustore_count_optim_0031_02 values ( seq_ustore_count_optim_0031.nextval , -1 , -2 , 15 , 4041.04329387 , ' A哪吒 ','1924-12-03 00:00:00.068000' ) ;
insert into t_ustore_count_optim_0031_02 values ( seq_ustore_count_optim_0031.nextval , 3  , 4 , 3 , 405.70237548 , ' 010012228 ' ,'1925-12-03 00:00:00.000000') ;
insert into t_ustore_count_optim_0031_02 values ( seq_ustore_count_optim_0031.nextval , -1 , 3 , 30 , 4030.1242131 , ' 数字字符字母 ','1924-12-03 00:00:04.000000' ) ;
insert into t_ustore_count_optim_0031_02 values ( seq_ustore_count_optim_0031.nextval , 4  , 6 , 4 , 401.87754238 , ' 010012228 ','1925-01-03 00:00:05.000000' ) ;
insert into t_ustore_count_optim_0031_02 values ( seq_ustore_count_optim_0031.nextval , 4  , 9 , 0 , 4046.284633 , ' 数字字符字母 ' ,'1929-09-03 00:00:32.000000') ;
insert into t_ustore_count_optim_0031_02 values ( seq_ustore_count_optim_0031.nextval , -2 , 6.6 , 4 , 4003.68558645 , ' 数字字符字母 ','1925-08-03 00:00:00.000000' ) ;
insert into t_ustore_count_optim_0031_02 values ( seq_ustore_count_optim_0031.nextval , -2 , -2 , null , 4044.9145853 , ' ' ,'1925-01-03 00:00:00.020000' ) ;
insert into t_ustore_count_optim_0031_02 values ( seq_ustore_count_optim_0031.nextval , 4  , 9 , 0 , 4035.26588922 , ' 010012228 ','1925-11-03 00:20:00.000000' ) ;
insert into t_ustore_count_optim_0031_02 values ( seq_ustore_count_optim_0031.nextval , -2 , 11 , 2 , 4011.51183805 , ' 010012228 ','1965-10-03 00:00:44.000000' ) ;
insert into t_ustore_count_optim_0031_02 values ( seq_ustore_count_optim_0031.nextval , 2  , 10 , 12 , 4018.35133361 , ' 数字字符字母 ','1925-01-03 00:00:00.000000' ) ;
insert into t_ustore_count_optim_0031_02 values ( seq_ustore_count_optim_0031.nextval , -2 , 7 , 32, 4041.62109974 , ' ilhfjejgYHG ','1925-01-03 00:00:25.000000' ) ;
insert into t_ustore_count_optim_0031_02 values ( seq_ustore_count_optim_0031.nextval , 4  , 1.0 , 11 , 4018.1233257 , ' 数字字符字母 ','1923-01-03 00:30:00.000246' ) ;
insert into t_ustore_count_optim_0031_02 values ( seq_ustore_count_optim_0031.nextval , 1.1  , 55 , 0 , 4018.75686387 , ' 010012228 ' ,'1925-02-03 00:00:10.000000') ;
insert into t_ustore_count_optim_0031_02 values ( seq_ustore_count_optim_0031.nextval , -2 , 7 , -1 , 4001.54253863 , ' gkj^%$ ( )+_-= ' ,'1925-02-03 04:00:00.000000') ;
insert into t_ustore_count_optim_0031_02 values ( seq_ustore_count_optim_0031.nextval , 2 , 2 , -5 , 4046.20763052 , ' ilhfjejgYHG ',null ) ;
insert into t_ustore_count_optim_0031_02 values ( seq_ustore_count_optim_0031.nextval , 8  , 13 , 1-3 , 4032.2835411454 , ' A哪吒 ' ,null) ;
insert into t_ustore_count_optim_0031_02 values ( seq_ustore_count_optim_0031.nextval , 34 , 12 , 30 , 40166.485186 , ' gkj^%$ ( )+_-= ' ,'1965-11-03 00:00:07.000000') ;
insert into t_ustore_count_optim_0031_02 values ( seq_ustore_count_optim_0031.nextval , 1  , 5.5 , -1 , 4007.07989431 , ' 数字字符字母 ' ,'1985-12-03 00:00:00.000000') ;
insert into t_ustore_count_optim_0031_02 values ( seq_ustore_count_optim_0031.nextval , -3  , 87 , 0 , 4075.5177643 , ' ilhfjejgYHG ' ,'1915-02-03 00:00:00.000000') ;
insert into t_ustore_count_optim_0031_02 values ( seq_ustore_count_optim_0031.nextval , 3  , 9 , 43 , 404.05504939 , ' ilhfjejgYHG ','1985-12-03 00:55:00.000000'  ) ;
insert into t_ustore_count_optim_0031_02 values ( seq_ustore_count_optim_0031.nextval , 2  , 11 , 21 , 40290.02593705 , ' ilhfjejgYHG ','1985-12-03 00:00:00.000000'  ) ;
insert into t_ustore_count_optim_0031_02 values ( seq_ustore_count_optim_0031.nextval ,null, 9 , null , 4004.0554939 , 'null' ,'1985-12-03 00:00:00.990000' ) ;
insert into t_ustore_count_optim_0031_02 values ( seq_ustore_count_optim_0031.nextval , -1 , -9 , 15 , 4041.04329387 , ' A哪吒 ' ,null) ;
insert into t_ustore_count_optim_0031_02 values ( seq_ustore_count_optim_0031.nextval ,null, -6 , 13 , 4005.70237548 , 'null','1925-12-03 06:10:34.000000' ) ;
insert into t_ustore_count_optim_0031_02 values ( seq_ustore_count_optim_0031.nextval ,null, 3 , 34 , 40030.1242131 , ' 数字字符字母 ','1925-12-03 06:10:00.000000' ) ;
insert into t_ustore_count_optim_0031_02 values ( seq_ustore_count_optim_0031.nextval ,-244, seq_ustore_count_optim_0031.nextval-4 , null , null , ' 01001222rgv8 ','1925-12-13 06:10:00.010000' ) ;
insert into t_ustore_count_optim_0031_02 values ( seq_ustore_count_optim_0031.nextval , 4  , 4 , 0 , 4046.2054633 , ' 数字字符字母 ' ,'1925-10-03 06:10:00.000000') ;
insert into t_ustore_count_optim_0031_02 values ( seq_ustore_count_optim_0031.nextval , -2 , 5 , -4 , null , ' 数字字符字母 ' ,'1925-12-03 06:10:04.000000') ;
insert into t_ustore_count_optim_0031_02 values ( seq_ustore_count_optim_0031.nextval , -2 , 2 , 3 , 4044.915853 , ' 数字字vrewv符字母 ','1925-12-03 06:10:00.000000' ) ;

--step6:按条件查找表数据; expect:执行成功
select * from t_ustore_count_optim_0031_01 order by 1,2,3,4  limit 5;

--step7:count(column)在dml中的应用-update; expect:执行成功
update t_ustore_count_optim_0031_01 set id4 = (select count(char1) over(partition by id1 order by id2) from t_ustore_count_optim_0031_02 where id1 =1) where id3 =4;

--step8:按条件查找表数据; expect:数据发送变化
select * from t_ustore_count_optim_0031_01 order by 1,2,3,4  limit 5;

--step9:清理环境; expect:执行成功
drop table if exists t_ustore_count_optim_0031_01;
drop table if exists t_ustore_count_optim_0031_02;
drop sequence if exists seq_ustore_count_optim_0031;